package niso;

import java.awt.Color;
import java.sql.*;

import javax.swing.JLabel;
import javax.swing.JOptionPane;

public class DBHandler {

	Connection Con;
	
	public DBHandler(int Portnum,String DBname,String User,String Pass){
		
			//Use the connector jar file contained in the project
			try{
				Class.forName("com.mysql.jdbc.Driver");
			}
			catch(ClassNotFoundException Ex){
				System.out.println("Problem: No Mysql Library found"+Ex);
			}			
			//Attempt to Establish a connection
			try{
				Con=DriverManager.getConnection("jdbc:mysql://localhost:"+Portnum+"/"+DBname,User,Pass);			
			}
			catch(SQLException SqlEx){
				//add(new CoursePane(),BorderLayout.CENTER);
				JOptionPane.showMessageDialog(null, "Error Wrong input or Sql connection is unestablished "+ SqlEx);
			}	
	}
	
	/*
	 * returns the result of a select statement
	 * The parameter is the Select statement
	 * An exception will occur if the select statement given is wrong and
	 * does not match the database connected to the DBHandler
	 */
	public ResultSet MySQLSelect(String Statement){
		try{
			PreparedStatement Select= Con.prepareStatement(Statement);
			ResultSet RS=Select.executeQuery();
			//Select.close();
			return RS;
		}catch(SQLException SqlEx){
			JOptionPane.showMessageDialog(null, "Sql Error");
			return null;
		}
	}
	
	public ResultSet MySQLSelect(String Statement,JLabel Lbl){
		try{
			PreparedStatement Select= Con.prepareStatement(Statement);
			ResultSet RS=Select.executeQuery();
			//Select.close();
			return RS;
		}catch(SQLException SqlEx){
			Lbl.setForeground(Color.RED);
			Lbl.setText("Sql Error in collecting data");
			return null;
		}
	}
	
	
	/*
	 * Use for insert statements on MySQL
	 * an integer is used to indicate if the insertion
	 * was successful
	 * 
	 *
	 * */
	public int MySQLinsert(String Statement){
		//initialize values needed
		int insertion=0;
		Statement Insert=null;
		
		try{
			Insert=Con.createStatement();
			insertion=Insert.executeUpdate(Statement);
			Insert.close();	//Close the statement to avoid memory leaks
		}catch(SQLException SQlEx){
			//prompt the user in case of error
			JOptionPane.showMessageDialog(null, "Error in insertion "+SQlEx);
		}
		
		//Indicate if insertion was successful
		return insertion;
	}
	
	public int MySQLinsert(String Statement,JLabel Lbl){
		//initialize values needed
		int insertion=0;
		Statement Insert=null;
		
		try{
			Insert=Con.createStatement();
			insertion=Insert.executeUpdate(Statement);
			Insert.close();	//Close the statement to avoid memory leaks
		}catch(SQLException SQlEx){
			//prompt the user in case of error
			Lbl.setForeground(Color.RED);
			Lbl.setText("Error in insertion ");
		}
		
		//Indicate if insertion was successful
		return insertion;
	}
	
	/*
	 * Use for updating and deleting records in the database
	 * MySQL Statements must be correct
	 * Errors will be handled elsewhere
	 * */
	public void MySQLUpdate(String Statement) throws SQLException{
		Statement UpdateStmt;
		
		
			UpdateStmt=Con.createStatement();
			UpdateStmt.executeUpdate(Statement);
			UpdateStmt.close();
		
	}
	
	
	public void MySQLUpdate(String Statement,JLabel Lbl) {
		Statement UpdateStmt;
			try{
				UpdateStmt=Con.createStatement();
				UpdateStmt.executeUpdate(Statement);
				UpdateStmt.close();
			}catch(SQLException SqE){
				Lbl.setText("Command error");
			}
			
		
	}
	
	
	//Return the connection
	public Connection getConnection(){ return Con;}
	
	//Close the connection from the MySQL server
	public void CloseConnection(){
		try {
			Con.close();
		} 	catch (SQLException e) {
		// TODO Auto-generated catch block
			
		}
	}
}
